﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace Royaume
{
    public class cSql
    {
        public SqlConnection myCn;
        protected SqlCommand myCmd;
        protected SqlDataReader myDr;
        protected SqlParameterCollection myParamList;
        public cSql(string server, string database, string user, string password)
        {
            
            this.myCn = new SqlConnection("Data Source =" + server + ";Initial Catalog=" + database + ";User Id=" + user + ";Password=" + password);

        }


        public bool tryConnection()
        {
            try
            {
                this.myCn.Open();
                this.myCn.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Impossible de se connecter au server");
            }
            return true;
        }

        public void fnStoredProc(string sStoredProcName, Dictionary<SqlParameter, object> maList)
        {
            try
            {
                this.myCn.Open();
                this.myCmd = new SqlCommand(sStoredProcName, this.myCn);
                foreach (KeyValuePair<SqlParameter, object> e in maList)
                {
                    myCmd.Parameters.Add(e.Key).Value = e.Value;
                }
                this.myCmd.CommandType = CommandType.StoredProcedure;
                this.myCmd.ExecuteScalar();
                this.myCn.Close();
            }
            catch (Exception ex) {
                
            }
        }

        public string fnExecScalar(string command)
        {
            this.myCmd = new SqlCommand(command, this.myCn);
            this.myCn.Open();
            string res = this.myCmd.ExecuteScalar().ToString();
            this.myCn.Close();
            return res;
        }

        public int fnExecScalarInt(string command)
        {
            this.myCmd = new SqlCommand(command, this.myCn);
            this.myCn.Open();
            this.myCmd.CommandType = CommandType.StoredProcedure;
            int res = (int)this.myCmd.ExecuteScalar();
            this.myCn.Close();
            return res;
        }

        public int fnExecScalarInt(string command, Dictionary<SqlParameter, object> maList)
        {
            this.myCmd = new SqlCommand(command, this.myCn);
            this.myCn.Open();
            foreach (KeyValuePair<SqlParameter, object> e in maList)
            {
                myCmd.Parameters.Add(e.Key).Value = e.Value;
            }
            this.myCmd.CommandType = CommandType.StoredProcedure;
            int res = (int)this.myCmd.ExecuteScalar();
            this.myCn.Close();
            return res;
        }

        public string fnExecSalarString(string command)
        {
            this.myCmd = new SqlCommand(command, this.myCn);
            this.myCn.Open();
            this.myCmd.CommandType = CommandType.Text;
            string res = this.myCmd.ExecuteScalar().ToString();
            this.myCn.Close();
            return res;
        }

        public string fnExecSalarString(string command, Dictionary<SqlParameter, object> maList)
        {
            this.myCmd = new SqlCommand(command, this.myCn);
            this.myCn.Open();
            foreach (KeyValuePair<SqlParameter, object> e in maList)
            {
                myCmd.Parameters.Add(e.Key).Value = e.Value;
            }
            this.myCmd.CommandType = CommandType.StoredProcedure;
            string res = this.myCmd.ExecuteScalar().ToString();
            this.myCn.Close();
            return res;
        }

        public SqlDataReader fnPeuplerLst(string Command, bool TrueIfStoredProc)
        {
            this.myCn.Open();
            if (TrueIfStoredProc == false)
            {
                this.myCmd = new SqlCommand(Command, this.myCn);
                this.myDr = this.myCmd.ExecuteReader();
            }
            else
            {

            }

            this.myCn.Close();
            return myDr;
        }

        public SqlDataReader fnStoreReader(string Command, Dictionary<SqlParameter, object> maList)
        {
            this.myCn.Open();
            this.myCmd = new SqlCommand(Command, this.myCn);
            this.myCmd.CommandType = CommandType.StoredProcedure;
            foreach (KeyValuePair<SqlParameter, object> e in maList)
            {
                myCmd.Parameters.Add(e.Key).Value = e.Value;
            }
            SqlDataReader myReader;
            myReader = this.myCmd.ExecuteReader();

            return myReader;
        }

        public SqlDataReader fnStoreReader(string Command)
        {
            this.myCn.Open();
            this.myCmd = new SqlCommand(Command, this.myCn);
            this.myCmd.CommandType = CommandType.StoredProcedure;
            
            SqlDataReader myReader;
            myReader = this.myCmd.ExecuteReader();

            return myReader;
        }

        public void fnCloseConnection()
        {

            if (this.myCn != null)
            {
                this.myCn.Close();
            }
        }
    }
}